A stored procedure, a database object executed on the Teradata
Database, is a combination of SQL statements and control and condition
handling statements that provide an interface to the Teradata Database.
Typically, a stored procedure consists of a:
Typically, a stored procedure consists of a:
- Procedure name
- Input and output parameters
- Procedure body.
How to Create Procedure
create procdure emp_procdure()
Begin
procedure body
end;
CREATE MULTISET VOLATILE TABLE US_STATES
, NO JOURNAL
, NO LOG,NO FALLBACK
(
STATE_NAME VARCHAR(13),
STATE_ABBR CHAR(2),
STATE_CAPITAL VARCHAR(13),
STATE_CITY VARCHAR(13)
)
PRIMARY INDEX(STATE_NAME)
ON COMMIT PRESERVE ROWS
;
CREATE PROCEDURE US_STATE_1 ()
BEGIN
INSERT INTO US_STATES VALUES('Alabama','AL','Montgomery','Birmingham');
INSERT INTO US_STATES VALUES('Alaska','AK','Juneau','Anchorage');
INSERT INTO US_STATES VALUES('Arizona','AZ','Phoenix','Phoenix');
INSERT INTO US_STATES VALUES('Arkansas','AR','LittleRock','LittleRock');
INSERT INTO US_STATES VALUES('California','CA','Sacramento','LosAngeles');
END;
How To execute Procedure
call US_STATE_1 ()
select * from US_STATE;
Declare variable in stored procedure in Teradata
Variable declarations in a Teradata Stored Procedure are always local to the surrounding compound statement (BEGIN…END), and all the compound statements which are located within the surrounding BEGIN…END.
REPLACE PROCEDURE SAMPLE_SP ( OUT n INTEGER )
BEGIN
DECLARE x INTEGER DEFAULT 99;
BEGIN
DECLARE x INTEGER DEFAULT 100;
SET n = x;
END;
END;
CALL SAMPLE_SP(x);
Error Handling in Teradata Stored Procedures
Error handling in Teradata Stored Procedures is condition based, having the same functionality offered by modern programming languages (C++, Java, etc.).Without the availability of state-based error handling one would need to implement and invoke code for each type of error and at each code location where the error could occur.
Not only is this a tremendous effort from a development point of view, but some error handling can easily be forgotten about. Below you can see one example of such a programmatic approach, handling division by zero errors.
REPLACE PROCEDURE DIVISION_BY_ZERO (IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10;
— Error Handling programmatically
CASE
WHEN n = 0 THEN SET x = 0; ELSE SET x = (x/ n);
END CASE;
END;
CALL DIVISION_BY_ZERO(0);
When using error handlers, the handler code for a certain error is defined once within the handler and executed automatically each time the error condition is raised. Below you see the division by zero problems solved with an error handler. The error code ‘22012’ is returned in case of a division by zero error:
REPLACE PROCEDURE DIVISION_BY_ZERO (IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10;
DECLARE EXIT HANDLER FOR SQLSTATE ‘22012’ SET x = 0; — Error Handler
SET x = (x/n);
END;
CALL DIVISION_BY_ZERO(0);
It is possible to define error handlers which are responsible for all errors or warnings (generic handlers), they will be executed if a particular error handler does not already cover an error/warning. The developer does not need to know in advance all possible kind of errors and warning.
Generic handler is usually used to do the cleanup before exiting a stored procedure. See below two examples for generic exception and warning handlers:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET x =0;
DECLARE CONTINUE HANDLER FOR SQLWARNING SET x =0;
Each stored procedure can have exactly
Within a stored procedure, all executed SQL statements return a status which can be: completed, warning or exception. Exceptions and warnings can be handled by an error handler.
The status of each executed SQL statement is returned in the read-only variable SQLSTATE. This available is implicitly available in each Stored Procedure usually, is used within generic error handlers to find out further details about an error or warning.
There exists another read-only variable called SQLCODE, but it is obsolete and should not be used anymore.
SQLSTATE consists of 5 characters. The first two characters define the error class. The remaining three characters represent the error.
Many of the error codes returned by Teradata are mapped 1:1 to the SQLSTATE variable (but not all of them). For example, the Teradata error code 3776 is assigned to T3776 (Error class T3 = Teradata 3).
There are two main types of error handlers: Exit handlers and continue handlers.
The Exit Handler
Whenever the execution of exit handler is finished, the normal code execution continues with the block (defined by BEGIN…END) which is surrounding the statement which caused the exception. Apparently, if there is no surrounding block, the stored procedure is terminating, but this is an exception. In general, don’t get fooled by the name “exit handler,” assuming the stored procedure will end. Only the block causing the handler execution is terminated, and the code execution is passed to the surrounding block.
The Continue Handler
The continue handler continues with the next statement after the one which triggered the handler execution.
Raising of conditions
Usually, the following events raise conditions which will trigger handler execution:
• SQL status exception or warning (after executing an SQL statement)
• The SIGNAL statement which is used to raise an exception programmatically
• The RESIGNAL statement passed by a continue handler to the surrounding block (BEGIN…END)
Handler Propagation
Handlers can be declared in each data block (BEGIN…END). Different data blocks can handle different errors or even the same errors. In the case of an error, the code blocks are searched for an appropriate handler, starting with the block were the error occurred until the outermost BEGIN…END is reached. If none of the code blocks contains a handler, the stored procedure will exit with an error.
In any case, after executing a continue handler, code execution persists in the data block where the error triggered. In the event of an exit handler, the code block containing the error triggering statement will be terminated, and code execution continues with the outer block (if any).
In case an error is raised within a handler, the handler execution is terminated, and the stored procedure tries to find another handler by continuing searching the data blocks from the innermost to the outermost. Be aware, that an error event in the handler will not try to solve this handler error but the original error which leads to the handler execution!
Below is an example for nested handlers. We nested three handlers for the division by zero condition. Intentionally we cause another division by zero exception (SET x=1/0) in two of the three handlers. Therefore the outermost handler finally is the one who will handle this error:
REPLACE PROCEDURE DIVISION_BY_ZERO ( IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10.00;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘22012’ SET x=0; — handles the error
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE ‘22012’ SET x = 1/0;
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE ‘22012’ SET x = 1/0;
SET x = x / n;
END;
END; END;
CALL DIVISION_BY_ZERO(0);
Activity_COUNT is used in Teradata Stored Procedure to keep track of the number of records impacted by any SQL Query. To check it, just use the ACTIVITY_COUNT keyword after any SQL Query which impacts rows. It is initialised to 0 at the start and value is updated after every single SQL query. Lets see an example below to see how Activity_Count works in Teradata:
REPLACE PROCEDURE SP_ACTIVITYCOUNT (OUT tot_count_delete INTEGER)
SQL SECURITY INVOKER
BEGIN
CREATE MULTISET VOLATILE TABLE vt_test1
(
name VARCHAR(20)
)
ON COMMIT PRESERVE ROWS;
INSERT INTO vt_test1 VALUES (‘Mike’);
INSERT INTO vt_test1 VALUES (‘Megan’);
INSERT INTO vt_test1 VALUES (‘Morgan’);
INSERT INTO vt_test1 VALUES (‘Mona’);
DELETE FROM vt_test1 WHERE name LIKE ‘M%n’;
SET tot_count_delete = ACTIVITY_COUNT;
END;
CALL SP_ACTIVITYCOUNT (tot_count_delete);
create procdure emp_procdure()
Begin
procedure body
end;
CREATE MULTISET VOLATILE TABLE US_STATES
, NO JOURNAL
, NO LOG,NO FALLBACK
(
STATE_NAME VARCHAR(13),
STATE_ABBR CHAR(2),
STATE_CAPITAL VARCHAR(13),
STATE_CITY VARCHAR(13)
)
PRIMARY INDEX(STATE_NAME)
ON COMMIT PRESERVE ROWS
;
CREATE PROCEDURE US_STATE_1 ()
BEGIN
INSERT INTO US_STATES VALUES('Alabama','AL','Montgomery','Birmingham');
INSERT INTO US_STATES VALUES('Alaska','AK','Juneau','Anchorage');
INSERT INTO US_STATES VALUES('Arizona','AZ','Phoenix','Phoenix');
INSERT INTO US_STATES VALUES('Arkansas','AR','LittleRock','LittleRock');
INSERT INTO US_STATES VALUES('California','CA','Sacramento','LosAngeles');
END;
How To execute Procedure
call US_STATE_1 ()
select * from US_STATE;
Declare variable in stored procedure in Teradata
Variable declarations in a Teradata Stored Procedure are always local to the surrounding compound statement (BEGIN…END), and all the compound statements which are located within the surrounding BEGIN…END.
REPLACE PROCEDURE SAMPLE_SP ( OUT n INTEGER )
BEGIN
DECLARE x INTEGER DEFAULT 99;
BEGIN
DECLARE x INTEGER DEFAULT 100;
SET n = x;
END;
END;
CALL SAMPLE_SP(x);
Error Handling in Teradata Stored Procedures
Error handling in Teradata Stored Procedures is condition based, having the same functionality offered by modern programming languages (C++, Java, etc.).Without the availability of state-based error handling one would need to implement and invoke code for each type of error and at each code location where the error could occur.
Not only is this a tremendous effort from a development point of view, but some error handling can easily be forgotten about. Below you can see one example of such a programmatic approach, handling division by zero errors.
REPLACE PROCEDURE DIVISION_BY_ZERO (IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10;
— Error Handling programmatically
CASE
WHEN n = 0 THEN SET x = 0; ELSE SET x = (x/ n);
END CASE;
END;
CALL DIVISION_BY_ZERO(0);
When using error handlers, the handler code for a certain error is defined once within the handler and executed automatically each time the error condition is raised. Below you see the division by zero problems solved with an error handler. The error code ‘22012’ is returned in case of a division by zero error:
REPLACE PROCEDURE DIVISION_BY_ZERO (IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10;
DECLARE EXIT HANDLER FOR SQLSTATE ‘22012’ SET x = 0; — Error Handler
SET x = (x/n);
END;
CALL DIVISION_BY_ZERO(0);
It is possible to define error handlers which are responsible for all errors or warnings (generic handlers), they will be executed if a particular error handler does not already cover an error/warning. The developer does not need to know in advance all possible kind of errors and warning.
Generic handler is usually used to do the cleanup before exiting a stored procedure. See below two examples for generic exception and warning handlers:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET x =0;
DECLARE CONTINUE HANDLER FOR SQLWARNING SET x =0;
Each stored procedure can have exactly
- One generic handler for exceptions (SQLEXCEPTION)
- One generic handler for warnings (SQLWARNING)
- One generic handler in case a cursor does not return any data (NOTFOUND)
- Per code block (BEGIN…END) and error code exactly one specific handler
Within a stored procedure, all executed SQL statements return a status which can be: completed, warning or exception. Exceptions and warnings can be handled by an error handler.
The status of each executed SQL statement is returned in the read-only variable SQLSTATE. This available is implicitly available in each Stored Procedure usually, is used within generic error handlers to find out further details about an error or warning.
There exists another read-only variable called SQLCODE, but it is obsolete and should not be used anymore.
SQLSTATE consists of 5 characters. The first two characters define the error class. The remaining three characters represent the error.
Many of the error codes returned by Teradata are mapped 1:1 to the SQLSTATE variable (but not all of them). For example, the Teradata error code 3776 is assigned to T3776 (Error class T3 = Teradata 3).
There are two main types of error handlers: Exit handlers and continue handlers.
The Exit Handler
Whenever the execution of exit handler is finished, the normal code execution continues with the block (defined by BEGIN…END) which is surrounding the statement which caused the exception. Apparently, if there is no surrounding block, the stored procedure is terminating, but this is an exception. In general, don’t get fooled by the name “exit handler,” assuming the stored procedure will end. Only the block causing the handler execution is terminated, and the code execution is passed to the surrounding block.
The Continue Handler
The continue handler continues with the next statement after the one which triggered the handler execution.
Raising of conditions
Usually, the following events raise conditions which will trigger handler execution:
• SQL status exception or warning (after executing an SQL statement)
• The SIGNAL statement which is used to raise an exception programmatically
• The RESIGNAL statement passed by a continue handler to the surrounding block (BEGIN…END)
Handler Propagation
Handlers can be declared in each data block (BEGIN…END). Different data blocks can handle different errors or even the same errors. In the case of an error, the code blocks are searched for an appropriate handler, starting with the block were the error occurred until the outermost BEGIN…END is reached. If none of the code blocks contains a handler, the stored procedure will exit with an error.
In any case, after executing a continue handler, code execution persists in the data block where the error triggered. In the event of an exit handler, the code block containing the error triggering statement will be terminated, and code execution continues with the outer block (if any).
In case an error is raised within a handler, the handler execution is terminated, and the stored procedure tries to find another handler by continuing searching the data blocks from the innermost to the outermost. Be aware, that an error event in the handler will not try to solve this handler error but the original error which leads to the handler execution!
Below is an example for nested handlers. We nested three handlers for the division by zero condition. Intentionally we cause another division by zero exception (SET x=1/0) in two of the three handlers. Therefore the outermost handler finally is the one who will handle this error:
REPLACE PROCEDURE DIVISION_BY_ZERO ( IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10.00;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘22012’ SET x=0; — handles the error
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE ‘22012’ SET x = 1/0;
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE ‘22012’ SET x = 1/0;
SET x = x / n;
END;
END; END;
CALL DIVISION_BY_ZERO(0);
Activity_COUNT is used in Teradata Stored Procedure to keep track of the number of records impacted by any SQL Query. To check it, just use the ACTIVITY_COUNT keyword after any SQL Query which impacts rows. It is initialised to 0 at the start and value is updated after every single SQL query. Lets see an example below to see how Activity_Count works in Teradata:
REPLACE PROCEDURE SP_ACTIVITYCOUNT (OUT tot_count_delete INTEGER)
SQL SECURITY INVOKER
BEGIN
CREATE MULTISET VOLATILE TABLE vt_test1
(
name VARCHAR(20)
)
ON COMMIT PRESERVE ROWS;
INSERT INTO vt_test1 VALUES (‘Mike’);
INSERT INTO vt_test1 VALUES (‘Megan’);
INSERT INTO vt_test1 VALUES (‘Morgan’);
INSERT INTO vt_test1 VALUES (‘Mona’);
DELETE FROM vt_test1 WHERE name LIKE ‘M%n’;
SET tot_count_delete = ACTIVITY_COUNT;
END;
CALL SP_ACTIVITYCOUNT (tot_count_delete);
No comments:
Post a Comment